Imports System
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
Imports System.Web.UI.WebControls
Imports ASPNET.StarterKit.BusinessLogicLayer

Namespace ASPNET.StarterKit.DataAccessLayer

    Public Class SQLDataAccess
        Inherits DataAccess

        Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))

        ' BASE CLASS IMPLEMENTATION

        ' CATEGORY

        Private Const SP_CATEGORY_CREATE As String = "aspnet_starterkits_CreateNewCategory"
        Private Const SP_CATEGORY_DELETE As String = "aspnet_starterkits_DeleteCategory"
        Private Const SP_CATEGORY_GETALLCATEGORIES As String = "aspnet_starterkits_GetAllCategories"
        Private Const SP_CATEGORY_GETCATEGORYBYPROJECTID As String = "aspnet_starterkits_GetCategoriesByProjectId"
        Private Const SP_CATEGORY_GETCATEGORYBYID As String = "aspnet_starterkits_GetCategoryById"
        Private Const SP_CATEGORY_GETCATEGORYBYNAMEANDPROJECT As String = "aspnet_starterkits_GetCategoryByNameAndProjectId"
        Private Const SP_CATEGORY_UPDATE As String = "aspnet_starterkits_UpdateCategories"

        Public Overrides Function CreateNewCategory(ByVal newCategory As Category) As Integer

            If newCategory Is Nothing Then
                Throw New ArgumentNullException("newCategory")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@CategoryAbbreviation", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Abbreviation)
            AddParamToSQLCmd(sqlCmd, "@CategoryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newCategory.EstimateDuration)
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name)
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId)

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_CREATE)
            ExecuteScalarCmd(sqlCmd)

            Return CInt(sqlCmd.Parameters("@ReturnValue").Value)

        End Function

        Public Overrides Function DeleteCategory(ByVal categoryId As Integer) As Boolean

            If categoryId <= DefaultValues.GetCategoryIdMinValue() Then
                Throw New ArgumentOutOfRangeException("categoryId")
            End If

            Dim sqlCmd As New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@CategoryIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, categoryId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_DELETE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Overrides Function GetAllCategories() As List(Of Category)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)

            Dim categoryList As New List(Of Category)()
            TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)

            Return categoryList

        End Function

        Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category

            If Id <= DefaultValues.GetCategoryIdMinValue() Then
                Throw New ArgumentOutOfRangeException("Id")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)

            Dim categoryList As New List(Of Category)()
            TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)

            If categoryList.Count > 0 Then
                Return categoryList(0)
            Else
                Return Nothing
            End If

        End Function

        Public Overrides Function GetCategoryByCategoryNameandProjectId(ByVal categoryName As String, ByVal projectId As Integer) As Category

            If projectId <= DefaultValues.GetProjectIdMinValue() Then
                Throw New ArgumentOutOfRangeException("Id")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, categoryName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYNAMEANDPROJECT)

            Dim categoryList As New List(Of Category)()
            TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)

            If categoryList.Count > 0 Then
                Return categoryList(0)
            Else
                Return Nothing
            End If

        End Function

        Public Overrides Function GetCategoriesByProjectId(ByVal projectId As Integer) As List(Of Category)

            If projectId <= DefaultValues.GetProjectIdMinValue() Then
                Throw New ArgumentOutOfRangeException("projectId")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYPROJECTID)

            Dim categoryList As New List(Of Category)()
            TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)

            Return categoryList

        End Function

        Public Overrides Function UpdateCategory(ByVal newCategory As Category) As Boolean

            If newCategory Is Nothing Then
                Throw New ArgumentNullException("newCategory")
            End If

            If newCategory.Id <= DefaultValues.GetCategoryIdMinValue() Then
                Throw New ArgumentOutOfRangeException("newCategory.Id")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.Id)
            AddParamToSQLCmd(sqlCmd, "@CategoryAbbreviation", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Abbreviation)
            AddParamToSQLCmd(sqlCmd, "@CategoryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newCategory.EstimateDuration)
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name)
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId)

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_UPDATE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        ' PROJECT

        Private Const SP_PROJECT_ADDUSERTOPROJECT As String = "aspnet_starterkits_AddUserToProject"
        Private Const SP_PROJECT_CREATE As String = "aspnet_starterkits_CreateNewProject"
        Private Const SP_PROJECT_DELETE As String = "aspnet_starterkits_DeleteProject"
        Private Const SP_PROJECT_DISABLE As String = "TimeTracker_DisableProject"
        Private Const SP_PROJECT_ENABLE As String = "TimeTracker_EnableProject"
        Private Const SP_PROJECT_GETALLPROJECTS As String = "aspnet_starterkits_GetAllProjects"
        Private Const SP_PROJECT_GETALLDELETEDPROJECTS As String = "TimeTracker_GetAllDisabledProjects"
        Private Const SP_PROJECT_GETAPROJECTBYID As String = "aspnet_starterkits_GetProjectById"
        Private Const SP_PROJECT_GETAPROJECTSBYMANAGERUSERNAME As String = "aspnet_starterkits_GetProjectByManagerUserName"
        Private Const SP_PROJECT_GETPROJECTSBYYSERNAME As String = "aspnet_starterkits_GetProjectByUserName"
        Private Const SP_PROJECT_GETPROJECTMEMBERS As String = "aspnet_starterkits_GetProjectMember"
        Private Const SP_PROJECT_REMOVEUSERFROMPROJECT As String = "aspnet_starterkits_RemoveUserFromProject"
        Private Const SP_PROJECT_UPDATE As String = "aspnet_starterkits_UpdateProject"

        Public Overrides Function AddUserToProject(ByVal projectId As Integer, ByVal userName As String) As Boolean

            If userName Is Nothing OrElse userName.Length = 0 Then
                Throw New ArgumentOutOfRangeException("userName")
            End If

            If projectId <= 0 Then
                Throw New ArgumentOutOfRangeException("projectId")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@MemberUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName)
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_ADDUSERTOPROJECT)
            ExecuteScalarCmd(sqlCmd)

            Dim resultValue As Integer = CInt(sqlCmd.Parameters("@ResultValue").Value)

            If resultValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function


        Public Overrides Function CreateNewProject(ByVal newProject As Project) As Integer

            If newProject Is Nothing Then
                Throw New ArgumentNullException("newProject")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@ProjectCreatorUserName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.CreatorUserName)
            AddParamToSQLCmd(sqlCmd, "@ProjectCompletionDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newProject.CompletionDate)
            AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 1000, ParameterDirection.Input, newProject.Description)
            AddParamToSQLCmd(sqlCmd, "@ProjectEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newProject.EstimateDuration)
            AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.ManagerUserName)
            AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.Name)

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_CREATE)
            ExecuteScalarCmd(sqlCmd)

            Return CInt(sqlCmd.Parameters("@ReturnValue").Value)

        End Function

        Public Overrides Function DeleteProject(ByVal projectID As Integer) As Boolean

            If projectID <= 0 Then
                Throw New ArgumentOutOfRangeException("projectID")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@ProjectIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, projectID)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_DELETE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Overrides Function DisableProject(ByVal projectID As Integer) As Boolean

            If projectID <= 0 Then
                Throw New ArgumentOutOfRangeException("projectID")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@ProjectIdToDisable", SqlDbType.Int, 0, ParameterDirection.Input, projectID)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_DELETE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Overrides Function EnableProject(ByVal projectID As Integer) As Boolean

            If projectID <= 0 Then
                Throw New ArgumentOutOfRangeException("projectID")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@ProjectIdToDisable", SqlDbType.Int, 0, ParameterDirection.Input, projectID)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_ENABLE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Overrides Function GetAllProjects() As List(Of Project)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETALLPROJECTS)

            Dim prjList As New List(Of Project)()
            TExecuteReaderCmd(Of Project)(sqlCmd, AddressOf TGenerateProjectListFromReader(Of Project), prjList)

            Return prjList
        End Function

        Public Overrides Function GetAllDisabledProjects() As List(Of Project)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETALLDELETEDPROJECTS)

            Dim prjList As New List(Of Project)()
            TExecuteReaderCmd(Of Project)(sqlCmd, AddressOf TGenerateProjectListFromReader(Of Project), prjList)

            Return prjList
        End Function

        Public Overrides Function GetProjectById(ByVal projectId As Integer) As Project

            If projectId <= 0 Then
                Throw New ArgumentOutOfRangeException("projectId")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTBYID)

            Dim prjList As New List(Of Project)()
            TExecuteReaderCmd(Of Project)(sqlCmd, AddressOf TGenerateProjectListFromReader(Of Project), prjList)

            If prjList.Count > 0 Then
                Return prjList(0)
            Else
                Return Nothing
            End If

        End Function

        Public Overrides Function GetProjectsByManagerUserName(ByVal userName As String) As List(Of Project)

            If userName Is Nothing OrElse userName.Length = 0 Then
                Throw New ArgumentOutOfRangeException("userName")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 256, ParameterDirection.Input, userName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTSBYMANAGERUSERNAME)

            Dim prjList As New List(Of Project)()

            TExecuteReaderCmd(Of Project)(sqlCmd, AddressOf TGenerateProjectListFromReader(Of Project), prjList)
            Return prjList

        End Function

        Public Overrides Function GetProjectMembers(ByVal Id As Integer) As List(Of String)

            If Id <= 0 Then
                Throw (New ArgumentOutOfRangeException("Id"))
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, Id)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETPROJECTMEMBERS)

            Dim userList As List(Of String) = New List(Of String)()
            TExecuteReaderCmd(Of String)(sqlCmd, AddressOf TGenerateUsertListFromReader(Of String), userList)

            Return userList

        End Function

        Public Overrides Function GetProjectsByUserName(ByVal userName As String) As List(Of Project)

            If userName Is Nothing OrElse userName.Length = 0 Then
                Throw New ArgumentOutOfRangeException("userName")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 256, ParameterDirection.Input, userName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETPROJECTSBYYSERNAME)

            Dim prjList As New List(Of Project)()
            TExecuteReaderCmd(Of Project)(sqlCmd, AddressOf TGenerateProjectListFromReader(Of Project), prjList)

            Return prjList

        End Function

        Public Overrides Function RemoveUserFromProject(ByVal projectId As Integer, ByVal userName As String) As Boolean

            If String.IsNullOrEmpty(userName) Then
                Throw New ArgumentOutOfRangeException("userName")
            End If

            If projectId <= 0 Then
                Throw New ArgumentOutOfRangeException("projectId")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@userName", SqlDbType.NVarChar, 0, ParameterDirection.Input, userName)
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_REMOVEUSERFROMPROJECT)
            ExecuteScalarCmd(sqlCmd)

            Dim resultValue As Integer = CInt(sqlCmd.Parameters("@ResultValue").Value)

            If resultValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Overrides Function UpdateProject(ByVal projectToUpdate As Project) As Boolean

            If projectToUpdate Is Nothing Then
                Throw New ArgumentNullException("projectToUpdate")
            End If

            If projectToUpdate.Id <= 0 Then
                Throw New ArgumentOutOfRangeException("projectToUpdate")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectToUpdate.Id)
            AddParamToSQLCmd(sqlCmd, "@ProjectCompletionDate", SqlDbType.DateTime, 0, ParameterDirection.Input, projectToUpdate.CompletionDate)
            AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 1000, ParameterDirection.Input, projectToUpdate.Description)
            AddParamToSQLCmd(sqlCmd, "@ProjectEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, projectToUpdate.EstimateDuration)
            AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 256, ParameterDirection.Input, projectToUpdate.ManagerUserName)
            AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 256, ParameterDirection.Input, projectToUpdate.Name)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_UPDATE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        ' TIME ENTRY

        Private SP_TIMEENTRY_CREATE As String = "aspnet_starterkits_CreateNewTimeEntry"
        Private SP_TIMEENTRY_DELETE As String = "aspnet_starterkits_DeleteTimeEntry"
        Private SP_TIMEENTRY_GETALLTIMEENTRIES As String = "aspnet_starterkits_GetAllTimeEntries"
        Private SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER As String = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUser"
        Private SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE As String = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUserAndDate"
        Private SP_TIMEENTRY_UPDATE As String = "aspnet_starterkits_UpdateTimeEntry"
        Private SP_TIMEENTRY_GETTIMEENTRYBYID As String = "aspnet_starterkits_GetTimeEntryById"

        Public Overrides Function CreateNewTimeEntry(ByVal newTimeEntry As TimeEntry) As Integer

            If newTimeEntry Is Nothing Then
                Throw New ArgumentNullException("newTimeEntry")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTimeEntry.CategoryId)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryCreatorUserName", SqlDbType.NText, 255, ParameterDirection.Input, newTimeEntry.CreatorUserName)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryDescription", SqlDbType.NText, 1000, ParameterDirection.Input, newTimeEntry.Description)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newTimeEntry.Duration)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEnteredDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTimeEntry.ReportedDate)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, newTimeEntry.UserName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_CREATE)
            ExecuteScalarCmd(sqlCmd)

            Return CInt(sqlCmd.Parameters("@ReturnValue").Value)

        End Function

        Public Overrides Function DeleteTimeEntry(ByVal timeEntryId As Integer) As Boolean

            If timeEntryId <= DefaultValues.GetTimeEntryIdMinValue() Then
                Throw New ArgumentOutOfRangeException("timeEntryId")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, timeEntryId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_DELETE)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If returnValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Overrides Function GetAllTimeEntries() As List(Of TimeEntry)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIES)

            Dim timeEntryList As New List(Of TimeEntry)()
            TExecuteReaderCmd(Of TimeEntry)(sqlCmd, AddressOf TGenerateTimeEntryListFromReader(Of TimeEntry), timeEntryList)

            Return timeEntryList

        End Function

        Public Overrides Function GetTimeEntries(ByVal projectId As Integer, ByVal userName As String) As List(Of TimeEntry)

            If projectId <= DefaultValues.GetTimeEntryIdMinValue() Then
                Throw (New ArgumentOutOfRangeException("projectId"))
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER)

            Dim timeEntryList As List(Of TimeEntry) = New List(Of TimeEntry)()
            TExecuteReaderCmd(Of TimeEntry)(sqlCmd, AddressOf TGenerateTimeEntryListFromReader(Of TimeEntry), timeEntryList)

            Return timeEntryList

        End Function

        Public Overrides Function GetTimeEntryById(ByVal timeEntryId As Integer) As TimeEntry

            If timeEntryId <= 0 Then
                Throw (New ArgumentOutOfRangeException("timeEntryId"))
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@TimeEntryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntryId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETTIMEENTRYBYID)

            Dim timeEntryList As New List(Of TimeEntry)()
            TExecuteReaderCmd(Of TimeEntry)(sqlCmd, AddressOf TGenerateTimeEntryListFromReader(Of TimeEntry), timeEntryList)

            If timeEntryList.Count > 0 Then
                Return timeEntryList(0)
            Else
                Return Nothing
            End If

        End Function

        Public Overrides Function GetTimeEntriesByUserNameAndDates(ByVal userName As String, ByVal startingDate As DateTime, ByVal endDate As DateTime) As List(Of TimeEntry)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@EndDate", SqlDbType.DateTime, 0, ParameterDirection.Input, endDate)
            AddParamToSQLCmd(sqlCmd, "@StartingDate", SqlDbType.DateTime, 0, ParameterDirection.Input, startingDate)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE)

            Dim timeEntryList As New List(Of TimeEntry)()
            TExecuteReaderCmd(Of TimeEntry)(sqlCmd, AddressOf TGenerateTimeEntryListFromReader(Of TimeEntry), timeEntryList)

            Return timeEntryList

        End Function

        Public Overrides Function UpdateTimeEntry(ByVal timeEntry As TimeEntry) As Boolean

            If timeEntry Is Nothing Then
                Throw (New ArgumentNullException("timeEntry"))
            End If


            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntry.Id)
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntry.CategoryId)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryDescription", SqlDbType.NText, 1000, ParameterDirection.Input, timeEntry.Description)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, timeEntry.Duration)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryEnteredDate", SqlDbType.DateTime, 0, ParameterDirection.Input, timeEntry.ReportedDate)
            AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 1000, ParameterDirection.Input, timeEntry.UserName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_UPDATE)
            ExecuteScalarCmd(sqlCmd)

            Dim resultValue As Integer = CInt(sqlCmd.Parameters("@ReturnValue").Value)
            If resultValue = 0 Then
                Return True
            Else
                Return False
            End If

        End Function

        ' USER REPORT

        Private SP_TIMEENTRY_GETUSERREPORT As String = "aspnet_starterkits_GetTimeEntryUserReport"
        Private SP_TIMEENTRY_GETUSERREPORTBYCATEGORY As String = "aspnet_starterkits_GetTimeEntryUserReportByCategoryId"

        Public Overrides Function GetUserReportsByProjectId(ByVal projectId As Integer) As List(Of UserReport)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORT)

            Dim userReport As New List(Of UserReport)()
            TExecuteReaderCmd(Of UserReport)(sqlCmd, AddressOf TGenerateUserReportListFromReader(Of UserReport), userReport)

            Return userReport

        End Function

        Public Overrides Function GetUserReportsByCategoryId(ByVal categoryId As Integer) As List(Of UserReport)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORTBYCATEGORY)

            Dim userReport As New List(Of UserReport)()
            TExecuteReaderCmd(Of UserReport)(sqlCmd, AddressOf TGenerateUserReportListFromReader(Of UserReport), userReport)

            Return userReport

        End Function

        ' USER TOTAL DURATION REPORT

        Private SP_TIMEENTRY_GETUSERREPORTBYUSER As String = "aspnet_starterkits_GetTimeEntryUserReportByUser"

        Public Overrides Function GetUserReportsByUserName(ByVal userName As String) As List(Of UserTotalDurationReport)

            Dim sqlCmd As SqlCommand = New SqlCommand()

            AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 256, ParameterDirection.Input, userName)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORTBYUSER)

            Dim userReport As New List(Of UserTotalDurationReport)()
            TExecuteReaderCmd(Of UserTotalDurationReport)(sqlCmd, AddressOf TGenerateUserReportListFromReader(Of UserTotalDurationReport), userReport)

            Return userReport

        End Function

        ' Application Setings
        Private SP_APPSETTING_GETAPPSETTING As String = "TimeTracker_GetAppSetting"
        Private SP_APPSETTING_SETAPPSETTING As String = "TimeTracker_SetAppSetting"

        Public Overrides Function GetAppSetting(ByVal key As String) As String

            If String.IsNullOrEmpty(key) Then
                Throw New ArgumentOutOfRangeException("key")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()

            'AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.NText, 256, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@key", SqlDbType.NText, 256, ParameterDirection.Input, key)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPSETTING_GETAPPSETTING)
            Dim returnValue As String = ExecuteScalerToString(sqlCmd)

            'Dim returnValue As String = Convert.ToString(sqlCmd.Parameters("@ReturnValue").Value)
            If String.IsNullOrEmpty(returnValue) Then
                Return String.Empty
            Else
                Return returnValue
            End If

        End Function

        Public Overrides Function SetAppSetting(ByVal key As String, ByVal value As String) As String

            If String.IsNullOrEmpty(key) Then
                Throw New ArgumentOutOfRangeException("key")
            End If
            If String.IsNullOrEmpty(value) Then
                Throw New ArgumentOutOfRangeException("key")
            End If

            Dim sqlCmd As SqlCommand = New SqlCommand()

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.NText, 256, ParameterDirection.ReturnValue, Nothing)
            AddParamToSQLCmd(sqlCmd, "@key", SqlDbType.NText, 256, ParameterDirection.Input, key)
            AddParamToSQLCmd(sqlCmd, "@value", SqlDbType.NText, 256, ParameterDirection.Input, value)
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_APPSETTING_SETAPPSETTING)
            ExecuteScalarCmd(sqlCmd)

            Dim returnValue As String = Convert.ToString(sqlCmd.Parameters("@ReturnValue").Value)
            If String.IsNullOrEmpty(returnValue) Then
                Return String.Empty
            Else
                Return returnValue
            End If


        End Function

        ' SQL HELPER METHODS

        Private Sub AddParamToSQLCmd(ByVal sqlCmd As SqlCommand, ByVal paramId As String, ByVal sqlType As SqlDbType, _
                                        ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, ByVal paramvalue As Object)

            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            If paramId = String.Empty Then
                Throw New ArgumentOutOfRangeException("paramId")
            End If

            Dim newSqlParam As SqlParameter = New SqlParameter()
            newSqlParam.ParameterName = paramId
            newSqlParam.SqlDbType = sqlType
            newSqlParam.Direction = paramDirection

            If paramSize > 0 Then
                newSqlParam.Size = paramSize
            End If

            If Not paramvalue Is Nothing Then
                newSqlParam.Value = paramvalue
            End If

            sqlCmd.Parameters.Add(newSqlParam)

        End Sub

        Private Sub ExecuteScalarCmd(ByVal sqlCmd As SqlCommand)

            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If

            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            Using cn As SqlConnection = New SqlConnection(Me.ConnectionString)

                sqlCmd.Connection = cn
                cn.Open()
                sqlCmd.ExecuteScalar()
            End Using

        End Sub

        Private Function ExecuteScalerToString(ByVal sqlCmd As SqlCommand) As String

            Dim retString As String

            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If

            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            Using cn As SqlConnection = New SqlConnection(Me.ConnectionString)

                sqlCmd.Connection = cn
                cn.Open()
                retString = sqlCmd.ExecuteScalar()
            End Using

            Return retString
        End Function

        Private Sub SetCommandType(ByVal sqlCmd As SqlCommand, ByVal cmdType As CommandType, ByVal cmdText As String)

            sqlCmd.CommandType = cmdType
            sqlCmd.CommandText = cmdText

        End Sub

        Private Sub TExecuteReaderCmd(Of T)(ByVal sqlCmd As SqlCommand, ByVal gcfr As TGenerateListFromReader(Of T), ByRef List As List(Of T))

            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If

            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            Using cn As SqlConnection = New SqlConnection(Me.ConnectionString)

                sqlCmd.Connection = cn
                cn.Open()
                gcfr(sqlCmd.ExecuteReader(), List)

            End Using

        End Sub

        ' LIST HELPER METHODS

        Private Sub TGenerateProjectListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef prjList As List(Of Project))

            Do While returnData.Read()

                Dim actualDuration As Decimal = 0
                If Not returnData("ProjectActualDuration") Is DBNull.Value Then
                    actualDuration = Convert.ToDecimal(returnData("ProjectActualDuration"))
                End If

                Dim project As Project = New Project(actualDuration, CStr(returnData("ProjectCreatorDisplayName")), _
                          CDate(returnData("ProjectCompletionDate")), CDate(returnData("ProjectCreationDate")), _
                          CStr(returnData("ProjectDescription")), CType(returnData("ProjectEstimateDuration"), Decimal), _
                          CInt(returnData("ProjectId")), CStr(returnData("ProjectManagerDisplayName")), CStr(returnData("ProjectName")))
                prjList.Add(project)

            Loop

        End Sub

        Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category))

            Do While returnData.Read()

                Dim actualDuration As Decimal = 0
                If Not returnData("CategoryActualDuration") Is DBNull.Value Then
                    actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration"))
                End If

                Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _
                          CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _
                          CInt(returnData("ProjectId")))
                categoryList.Add(category)

            Loop

        End Sub

        Private Sub TGenerateTimeEntryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef timeEntryList As List(Of TimeEntry))

            Do While returnData.Read()

                Dim timeEntry As TimeEntry = New TimeEntry(CStr(returnData("TimeEntryCreatorDisplayName")), _
                          CInt(returnData("CategoryId")), CDate(returnData("TimeEntryCreated")), CStr(returnData("TimeEntryDescription")), _
                          CType(returnData("TimeEntryDuration"), Decimal), CInt(returnData("TimeEntryId")), CDate(returnData("TimeEntryDate")), _
                          CStr(returnData("TimeEntryUserName")))
                timeEntryList.Add(timeEntry)

            Loop

        End Sub

        Private Sub TGenerateUsertListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef userList As List(Of String))

            Do While returnData.Read()

                userList.Add(CStr(returnData("UserName")))

            Loop

        End Sub

        Private Sub TGenerateUserReportListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef userReportList As List(Of UserReport))

            Do While returnData.Read()

                Dim userReport As UserReport = New UserReport(CDec(returnData("duration")), CInt(returnData("CategoryId")), CStr(returnData("UserName")))
                userReportList.Add(userReport)

            Loop

        End Sub

        Private Sub TGenerateUserReportListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef userReportList As List(Of UserTotalDurationReport))

            Do While returnData.Read()

                Dim totalDuration As Decimal = 0
                If Not returnData("TotalDuration") Is DBNull.Value Then
                    totalDuration = CDec(returnData("TotalDuration"))
                End If
                Dim userReport As UserTotalDurationReport = New UserTotalDurationReport(totalDuration, CStr(returnData("UserName")))
                userReportList.Add(userReport)

            Loop

        End Sub


    End Class
  
End Namespace
